﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Collections;
using System.Windows.Forms;
using System.Xml;
using System.Xml.Schema;

namespace XML_Programming_Assignment.Class
{
    class DBManager
    {
        public static bool checkValid = true;

        //declaring connection string for database 
        public const string DBCONNSTR = "server=localhost;database=DavidSCM;integrated security=true";
        public static SqlConnection conn = new SqlConnection();
        public static SqlCommand comm = new SqlCommand();
        
        public static ArrayList quotationItemList_1 = new ArrayList();
        public static ArrayList quotationList_1 = new ArrayList();

        //Get all the product list details
        public static ArrayList AllProductRetrieve()
        {
            ArrayList result = new ArrayList();
            try
            {
                conn.ConnectionString = DBCONNSTR;
                conn.Open();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM Product";
                SqlDataReader reader = comm.ExecuteReader();

                while (reader.Read())
                {
                    string ProductID = reader["ProductID"].ToString();
                    string ProductName = reader["ProductName"].ToString();
                    string Manufacturer = reader["Manufacturer"].ToString();
                    string Category = reader["Category"].ToString();
                    double CurrentPrice = Convert.ToDouble(reader["CurrentPrice"].ToString());
                    int UnitsInStock = Convert.ToInt32(reader["UnitsInStock"].ToString());
                    int UnitsToOrder = Convert.ToInt32(reader["UnitsToOrder"].ToString());
                    int ReorderLevel = Convert.ToInt32(reader["ReorderLevel"].ToString());

                    Product product = new Product(ProductID, ProductName, Manufacturer, Category, CurrentPrice, UnitsInStock, UnitsToOrder, ReorderLevel);
                    result.Add(product);
                }
                reader.Close();
            }
            catch (SqlException ex)
            {
                MessageBox.Show("An error occured during collecting product information." + "\r\n" + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            finally
            {
                conn.Close();
            }
            return result;            
        }

        //Get the product details with low stock level
        public static ArrayList LowProductStockRetrieve()
        {
            ArrayList result = new ArrayList();
            try
            {
                conn.ConnectionString = DBCONNSTR;
                conn.Open();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM Product WHERE UnitsInStock < ReorderLevel";
                SqlDataReader reader = comm.ExecuteReader();

                while (reader.Read())
                {
                    string ProductID = reader["ProductID"].ToString();
                    string ProductName = reader["ProductName"].ToString();
                    string Manufacturer = reader["Manufacturer"].ToString();
                    string Category = reader["Category"].ToString();
                    double CurrentPrice = Convert.ToDouble(reader["CurrentPrice"].ToString());
                    int UnitsInStock = Convert.ToInt32(reader["UnitsInStock"].ToString());
                    int UnitsToOrder = Convert.ToInt32(reader["UnitsToOrder"].ToString());
                    int ReorderLevel = Convert.ToInt32(reader["ReorderLevel"].ToString());

                    Product product = new Product(ProductID, ProductName, Manufacturer, Category, CurrentPrice, UnitsInStock, UnitsToOrder, ReorderLevel);
                    result.Add(product);
                }
                reader.Close();
            }
            catch (SqlException ex)
            {
                MessageBox.Show("An error occured during retrieving product information" + "\r\n" + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        //Get the data in Quotation XML file
        public static void readQuotation(string filePath)
        {
            try
            {
                //XmlReader reader = XmlReader.Create(filePath);
                XmlDocument xmlDoc = new XmlDocument();
                XmlNodeList quotationList = xmlDoc.GetElementsByTagName("Quotation");
                xmlDoc.Load(filePath);

                int quotationNum = 0;
                string quotationDate = "";                
                int sellerID = 0;
                int buyerID = 0;
                int rfqNumber = 0;
                string itemID = "";
                int quantity = 0;
                double unitPrice = 0.0;
                double totalPrice = 0.0;
                double totalItemPrice = 0.0;
                double shippingCharge = 0.0;
                double totalCost = 0.0;

                quotationItemList_1.Clear();
                quotationList_1.Clear();

                //Manipulate the the quotation objects in quotation and quotation_item from their arraylist
                foreach (XmlNode quotation in quotationList)
                {
                    if (quotation.Attributes.Count > 0)
                    {
                        quotationNum = Convert.ToInt32(quotation.Attributes[0].Value.ToString());
                    }
                    if (quotation.HasChildNodes)
                    {
                        XmlElement childNode = (XmlElement)quotation.FirstChild;

                        for (int i = 0; i < quotation.ChildNodes.Count; i++)
                        {
                            switch (childNode.Name)
                            {
                                case "Date":
                                    quotationDate = childNode.InnerText;
                                    break;

                                case "SellerID":
                                    sellerID = Convert.ToInt32(childNode.InnerText);
                                    break;

                                case "BuyerID":
                                    buyerID = Convert.ToInt32(childNode.InnerText);
                                    break;

                                case "RFQID":
                                    rfqNumber = Convert.ToInt32(childNode.InnerText);
                                    break;

                                case "Items":
                                    {
                                        //XmlDocument xmlDoc_Item = new XmlDocument();
                                        XmlNodeList quotationItemList = xmlDoc.GetElementsByTagName("Item");

                                        foreach (XmlNode item in quotationItemList)
                                        {
                                            if (item.Attributes.Count > 0)
                                            {
                                                itemID = item.Attributes[0].Value.ToString();
                                            }
                                            if (item.HasChildNodes)
                                            {
                                                XmlElement itemChildNode = (XmlElement)item.FirstChild;

                                                for (int j = 0; j < item.ChildNodes.Count; j++)
                                                {
                                                    switch (itemChildNode.Name)
                                                    {
                                                        case "Quantity":
                                                            quantity = Convert.ToInt32(itemChildNode.InnerText);
                                                            break;

                                                        case "UnitPrice":
                                                            unitPrice = Convert.ToDouble(itemChildNode.InnerText);
                                                            break;

                                                        case "TotalPrice":
                                                            totalPrice = Convert.ToDouble(itemChildNode.InnerText);
                                                            totalItemPrice = totalItemPrice + totalPrice;
                                                            break;
                                                    }
                                                    itemChildNode = (XmlElement)itemChildNode.NextSibling;
                                                }
                                                QuotationItem quotationItem = new QuotationItem(itemID, quotationNum, quantity, unitPrice, totalPrice);
                                                quotationItemList_1.Add(quotationItem);
                                            }
                                        }
                                    }
                                    break;

                                case "ShippingCharges":
                                    shippingCharge = Convert.ToDouble(childNode.InnerText);
                                    break;

                                case "TotalCost":
                                    totalCost = Convert.ToDouble(childNode.InnerText);
                                    break;
                            }
                            childNode = (XmlElement)childNode.NextSibling;
                        }
                        Quotation quotation_1 = new Quotation(quotationNum, quotationDate, rfqNumber, sellerID, totalItemPrice, shippingCharge, totalCost);
                        quotationList_1.Add(quotation_1);
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("An error occured during running" + "\r\n" + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
        }

        //Save the information from the Quotation xml file to the database
        public static void saveQuotation()
        {
            try
            {
                conn.ConnectionString = DBCONNSTR;
                conn.Open();
                comm.Connection = conn;
                string quotationQuery = "INSERT INTO Quotation (QuotationNum, QuotationDate, RFQID, SellerID, ItemTotalPrice, ShippingCharges, QuotationTotal) VALUES (@quotationNum, @quotationDate, @rfqID, @sellerID, @itemTotalPrice, @shippingCharges, @quotationTotal)";
                string quotationItemQuery = "INSERT INTO QuotationItem (ProductID, QuotationNum, Quantity, UnitPrice, TotalPrice) VALUES (@productID, @quotationNum, @quantity, @unitPrice, @totalPrice)";

                foreach (Quotation quotation in quotationList_1)
                {
                    comm = new SqlCommand(quotationQuery, conn);
                    comm.Parameters.AddWithValue("@quotationNum", quotation.QuotationNum);
                    comm.Parameters.AddWithValue("@quotationDate", quotation.QuotationDate);
                    comm.Parameters.AddWithValue("@rfqID", quotation.RFQID);
                    comm.Parameters.AddWithValue("@sellerID", quotation.SellerID);
                    comm.Parameters.AddWithValue("@itemTotalPrice", quotation.ItemTotalPrice);
                    comm.Parameters.AddWithValue("@shippingCharges", quotation.ShippingCharges);
                    comm.Parameters.AddWithValue("@quotationTotal", quotation.QuotationTotal);
                    comm.ExecuteNonQuery();
                }

                foreach (QuotationItem quotationItem in quotationItemList_1)
                {
                    comm = new SqlCommand(quotationItemQuery, conn);
                    comm.Parameters.AddWithValue("@productID", quotationItem.ProductID);
                    comm.Parameters.AddWithValue("@quotationNum", quotationItem.QuotationNum);
                    comm.Parameters.AddWithValue("@quantity", quotationItem.Quantity);
                    comm.Parameters.AddWithValue("@unitPrice", quotationItem.UnitPrice);
                    comm.Parameters.AddWithValue("@totalPrice", quotationItem.TotalPrice);
                    comm.ExecuteNonQuery();
                }
            }
            catch (SqlException ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                conn.Close();
                MessageBox.Show("Quotation is successfully saved into Database.");
            }
        }

        //Calling method when there is encountered errors in validating with the XML file and schema (called by validateXml())
        public static void ValidationCallBack(Object sender, ValidationEventArgs args)
        {
            //Display the validation error.  This is only called on error

            checkValid = false; //Validation failed
            AllFormClass.validateErrorList.Add(args.Message);
            
            //MessageBox.Show("Validation error: " + args.Message);
        }

        //Validate the RFQ XML file with its schema
        public static bool validateXml(String infile)
        {
            try
            {
                //First we create the xmltextreader

                XmlTextReader xmlr = new XmlTextReader(infile);
                //We pass the xmltextreader into the xmlvalidatingreader
                //This will validate the xml doc with the schema file
                //NOTE the xml file it self points to the schema file

                XmlValidatingReader xmlvread = new XmlValidatingReader(xmlr);

                // Set the validation event handler

                //xmlvread.ValidationEventHandler += new ValidationEventHandler(ValidationCallBack);
                xmlvread.ValidationEventHandler += new ValidationEventHandler(ValidationCallBack);
                checkValid = true; //make sure to reset the success var


                // Read XML data
                while (xmlvread.Read()) { }
                //Close the reader.
                xmlvread.Close();
                             
            }
            catch (Exception ex)
            {
                MessageBox.Show("An error occured during validation." + "\r\n" + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }            
                return checkValid;
           
        }       
    }
}
